package matlap.database.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.OrderDAO;
import matlap.shared.Order;

public class OrderJdbcDAO implements OrderDAO {

	Connection con = null;
	Statement st = null;
	ResultSet rs = null;
	private String query;

	/**
	 * Turns back the orders number FROM the orders table.
	 */

	public int getOrdersNumber() {

		int nr = 0;
		String query = "SELECT COUNT(username) AS darab FROM orders";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				nr = Integer.parseInt(rs.getString("darab"));
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return nr;
	}

	/**
	 * Turns back all of the orders in Order list.
	 */

	public List<Order> getOrders() {

		List<Order> list = new ArrayList<Order>();
		String firstName;
		String lastName;
		String username;
		String date;
		int nr;

		String query = "SELECT last_name, first_name, orders_date, number_of_pieces, people.username AS username FROM people,orders WHERE "
				+ " orders.username=people.username ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				date = rs.getString("orders_date");
				username = rs.getString("username");
				nr = rs.getInt("number_of_pieces");

				Order teach = new Order(lastName + " " + firstName, username,
						date, nr);

				list.add(i, teach);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;

	}

	/**
	 * Turns back the orders name in Order list.
	 */

	public List<Order> selectOrdersName() {

		List<Order> list = new ArrayList<Order>();
		String firstName;
		String lastName;
		String username;
		String query = "SELECT last_name,first_name, people.username AS username FROM people,orders WHERE role=3 AND orders.username=people.username ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				username = rs.getString("username");
				Order stu = new Order(lastName + " " + firstName, username,
						null, 0);

				list.add(i, stu);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;

	}

	/**
	 * Turns back orders in Order list by adding orders name.
	 */

	public List<Order> getOrdersByName(String orderName) {

		List<Order> list = new ArrayList<Order>();
		String firstname;
		String lastname;
		String username;
		String date;

		int nr;
		if (orderName.length() != 0) {
			String[] n = orderName.split(" ");
			if (n.length == 2)
				query = "SELECT last_name, first_name, orders_date, number_of_pieces, people.username AS username FROM people,orders WHERE "
						+ " orders.username=people.username AND ((first_name like '%"+ n[0]	+ "%' AND last_name like '%"
						+ n[1]+ "%') OR (first_name like '%"+ n[1]+ "%' AND last_name like '%" + n[0] + "%'))";
			else
				query = "SELECT last_name, first_name, orders_date, number_of_pieces, people.username AS username FROM people,orders WHERE "
						+ " orders.username=people.username AND (first_name like '%"+ orderName+ "%' OR last_name like '%"+ orderName+ "%')";

		} else
			query = "SELECT last_name, first_name, orders_date, number_of_pieces, people.username AS username FROM people,orders WHERE "
					+ " orders.username=people.username";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstname = rs.getString("first_name");
				lastname = rs.getString("last_name");
				username = rs.getString("username");
				date = rs.getString("orders_date");
				nr = rs.getInt("number_of_pieces");
				Order teach = new Order(lastname + " " + firstname, username,
						date, nr);

				list.add(i, teach);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * Inserts orders INTO orders table by adding Order.
	 */

	@Override
	public void insertOrder(Order order) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = new String("INSERT INTO orders VALUES('"
					+ order.getUserName() + "','" + order.getDate() + "','"
					+ order.getNr() + "')");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * Deletes orders FROM orders by adding Order.
	 */

	@Override
	public void deleteOrder(Order order) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = new String("delete FROM orders WHERE username='"
					+ order.getUserName() + "' AND orders_date='"
					+ order.getDate() + "'");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * Updates orders in orders table by adding Order.
	 */

	@Override
	public void updateOrder(Order order) {
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = new String("update orders set number_of_pieces='"
					+ order.getNr() + "' WHERE username='"
					+ order.getUserName() + "' AND orders_date='"
					+ order.getDate() + "'");
			st.executeUpdate(s);

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	@Override
	public List<Order> getMyOrders(String un) {
		List<Order> list = new ArrayList<Order>();
		String firstName;
		String lastName;
		String username;
		String date;
		int nr;

		String query = "SELECT last_name, first_name, orders_date, number_of_pieces, people.username AS username FROM people,orders WHERE "
				+ " orders.username=people.username AND people.username='"
				+ un
				+ "' ORDER BY last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {
				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				date = rs.getString("orders_date");
				username = rs.getString("username");
				nr = rs.getInt("number_of_pieces");
				Order teach = new Order(lastName + " " + firstName, username,
						date, nr);

				list.add(i, teach);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;

	}

	@Override
	public int getMyOrdersNumber(String un) {
		int nr = 0;
		String query = "SELECT COUNT(username) AS darab FROM orders WHERE username='"
				+ un + "'";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				nr = Integer.parseInt(rs.getString("darab"));
			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return nr;
	}

}
